package org.biojava.services.das.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import javax.sql.DataSource;

import org.biojava.dasobert.das.Capabilities;
import org.biojava.dasobert.dasregistry.Das1Source;
import org.biojava.dasobert.dasregistry.DasSource;
import org.biojava.services.das.registry.DasRegistrySql;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

public class DasSourceDAO extends NamedParameterJdbcDaoSupport{
	
	private static String BASIC_SELECT = "SELECT "
		+ " distinct(r.auto_id),r.url,r.adminemail,r.description,r.registerDate,"
		+ " r.leaseDate, r.helperurl, r.testcode, r.nickname, r.prefix, r.alert_admin, r.specification "
		+ " from registry r";

	public List <DasSource> getInactiveSources(){ 
	SqlParameterSource namedParameters = new MapSqlParameterSource("id", "");
	String sqlServers = BASIC_SELECT + " where active=0";
	return getNamedParameterJdbcTemplate().query(sqlServers,
			namedParameters, new ParameterizedRowMapper<DasSource>() {
				public DasSource mapRow(ResultSet row, int rowNum)
						throws SQLException {
					DasSource ds=new Das1Source();
					int auto_id = row.getInt("r.auto_id");
					String url = row.getString("r.url");
					String adminemail = row.getString("r.adminemail");
					String description = row.getString(4);
					Timestamp regTime = row.getTimestamp(5);
					Timestamp leaseTime = row.getTimestamp(6);
					String helperurl = row.getString(7);
					String nickname = row.getString(9);
					String thisprefix = row.getString(10);
					String alertMe = row.getString(11);
					String specification = row.getString("specification");
					 //System.out.println("DasSourceManager: converting the dates");
					java.util.Date registerDate = new Date(regTime.getTime());
					java.util.Date leaseDate = new Date(leaseTime.getTime());
					//ds.setId(idstring);
					ds.setUrl(url);
					ds.setAdminemail(adminemail);
					ds.setDescription(description);
					//ds.setCoordinateSystem(cs_names);
					//ds.setCapabilities(capabilities);
					//ds.setValidCapabilities(validCapabilities);
					ds.setRegisterDate(registerDate);
					ds.setLeaseDate(leaseDate);
					ds.setHelperurl(helperurl);
					if (specification != null) {
						ds.setSpecification(specification);
					} else {
						ds.setSpecification("Not specified by server");
					}
					// now part of coordinate system
					// ds.setTestCode(testcode);
					//ds.setLabels(labels);
					ds.setNickname(nickname);
					if (alertMe.equals("1"))
						ds.setAlertAdmin(true);
					else {
						ds.setAlertAdmin(false);
					}


					return ds;
				}
			});
	}
	/**
	 * 
	 * @param sourceUrl ending in a "/"
	 * @param serverId
	 * @return
	 */
	public String addServerIdToSource(String sourceUrl, int serverId) {
		String sql = "update registry set server_id=:server_id where url=:url";		
		String errors="";
	Map<String, Comparable> ps = new HashMap<String, Comparable>();
	ps.put("url",sourceUrl);
	ps.put("server_id", serverId);
	try {
		int value = getNamedParameterJdbcTemplate().update(sql, ps);
		if(value!=1){
			errors="There was an error writing the server_id to the database";
		}
	} catch (DataAccessException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	System.out.println(errors);
		return errors;
		
	}
	
	public int getServerIdForSourceUrl(String sourceUrl){
		String cmd = "select server_id from registry where url =:url";
		Map<String, String> params=new HashMap<String ,String>();
		params.put("url", sourceUrl);
		int serverId=getNamedParameterJdbcTemplate().queryForInt(cmd, params);
		return serverId;
	}
	
	
	
	public int getAutoIdForURL(String url) {
		String cmd = "select auto_id from registry where url =:url";
		Map<String, String> params=new HashMap<String ,String>();
		params.put("url", url);
		try {
			return getNamedParameterJdbcTemplate().queryForInt(cmd, params);
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			return DasSourceManager.UNKNOWN_AUTO_ID;
		}
		
		
	}
	public String setActiveSource(int autoId, boolean active) {
		String cmd2 = "update registry set active=:active where auto_id =:auto_id ";
		//Integer integer=new Integer(autoId);
	Map<String, Integer> params=new HashMap<String,Integer>();
	params.put("auto_id", autoId );
	if(active){
		params.put("active", 1 );
	}else{
		params.put("active", 0);
	}
	
	int result=getNamedParameterJdbcTemplate().update(cmd2, params);
 if(result==1){
	 return "";
 }else{
	 return "error setting source to be inactive ";
 }

	}
	public boolean isServiceActive(int auto_id) {
		String cmd = "select active from registry where auto_id =:auto_id";
		Map<String, Integer> params=new HashMap<String ,Integer>();
		params.put("auto_id", auto_id);
		int result= getNamedParameterJdbcTemplate().queryForInt(cmd, params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	public String setTimesForDataSource(Map<Capabilities, Long> times,
			int auto_id) {
		if(times==null || times.size()==0){
			return "No times found for capabilities";
		}
		
		String deleteSql="delete from capability_times where auto_id="+auto_id;
		int numberOfRowsAffected=getNamedParameterJdbcTemplate().update(deleteSql, new HashMap());
		System.out.println("deleted ok number of rows="+numberOfRowsAffected);
		
		

		String start = "insert into capability_times ( auto_id ";
		String middle=") values (";
		String end=" "+auto_id;
		

		//Integer integer=new Integer(autoId);
	Map<String, Long> params=new HashMap<String,Long>();
	
	for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
		start+=","+value.getCommand()+" ";
		end+=",:"+value.getCommand();
	      params.put(value.getCommand(), times.get(value));
	  }
	end+=")";
//	params.put("auto_id", autoId );
//	if(active){
//		params.put("active", 1 );
//	}else{
//		params.put("active", 0);
//	}
	String sql=start+middle+end;
	System.out.println("times update sql="+sql);
	int result=getNamedParameterJdbcTemplate().update(sql, params);
	if(result==1){
		 return "";
	 }else{
		 return "error setting source to be inactive ";
	 }
	}
	
	
	public Map<Capabilities, Long> getCapabilityTimes(int auto_id) {
		
		String startsql="select ";
		String select=" auto_id";
		for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
			select+=","+value.getCommand()+" ";
			
		      //params.put(value.getCommand(), times.get(value));
		  }
		String end=" from capability_times  where auto_id="+auto_id;
		String sql=startsql+select+end;
		//System.out.println(sql);
		Map<Capabilities, Long> times=new HashMap<Capabilities,Long>();
		try {
			times = (Map<Capabilities, Long>) this.getJdbcTemplate().queryForObject(sql,
			
				    new RowMapper() {
				    	Map<Capabilities, Long> times=new HashMap<Capabilities,Long>();

				        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				        	for (Capabilities value : EnumSet.allOf(Capabilities.class)) {
				        		times.put(value, rs.getLong(value.getCommand()));
				    			
				    		      //params.put(value.getCommand(), times.get(value));
				    		  }
				            
				        
				            return times;
				        }
				    });
		} catch (DataAccessException e) {
			//e.printStackTrace();
			//if no result returned spring throws exception which is a bit rubbish and so we just return a new made up object
			return new HashMap<Capabilities,Long>();
			
		}
		return times;
		
	}
	/**
	 * delete a das source - only to be used to clean up after JUnit testing of creation of DAS source
	 * @param auto_id
	 */
			
	public int deleteSource(int auto_id) {
	String sql="delete from registry where auto_id=:auto_id";
	Map<String,Integer> paramMap=new HashMap<String, Integer>();
	paramMap.put("auto_id", auto_id);
		return getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	
	
	
}
